package com.framework.excelTools; 

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.collections.map.ListOrderedMap;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;

import com.framework.OptionDictSupport;
import com.framework.jqgrid.JsonUtil;

public class DoExcel{
	
	private JdbcTemplate yzJdbcTemplate;
	public static String cols;
	public static String colsname;
	private static final Log log = LogFactory.getLog(DoExcel.class);
	public static void excelExport(HttpServletRequest httpservletrequest,
			List<?> listdata, OutputStream outputstream) throws Exception {

		// 创建新的Excel 工作簿
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 在Excel 工作簿中建一工作表
		HSSFSheet sheet = workbook.createSheet("Sheet1");
		// 设置单元格格式(文本)
		HSSFCellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("@"));
		String header_ = httpservletrequest.getParameter("header");
		 if(colsname!=null&&!colsname.equals("")){
		    	header_ =colsname;
		    }
		String[] headers = header_.split(",");
		String headermates_ = httpservletrequest.getParameter("headermate");
		if(cols!=null&&!cols.equals("")){
	    	headermates_ =cols;
	    }
		String[] headermates = headermates_.split(",");
		// 在索引0的位置创建行（第一行）
		HSSFRow row = sheet.createRow(0);

		int ii = 0;// 有些列是隐藏的，所以ii为列的真正列号
		for (int i = 0; i < headers.length; i++) {
			String[] headermate = headermates[i].split("@");// 截取列名，获取列的属性值，例如：列名，是否为隐藏字段
			if (headermate[1].equals("true")) {// true为隐藏字段
				continue;
			}
			if (headermate[0].equals("act")) {
				continue;
			}
			HSSFCell cell = row.createCell(ii);// 第一列，标题
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(headers[i]);
			ii++;
		}

		try {
			int k = 1;// 行号
			// System.out.println("输出第"+k+"行");
			for (Iterator<?> iterator = listdata.iterator(); iterator.hasNext();) {

				Object obj2 = iterator.next();
				String jsons = JsonUtil.obj2Json(obj2, k, listdata);
				// json后面是“,”去掉
				if (",".equals(jsons.substring(jsons.length() - 1,
						jsons.length()))) {
					jsons = jsons.substring(0, jsons.length() - 1);
				}

				// 设置j行从第二行开始
				row = sheet.createRow(k);
				Map<String, String> map = JsonUtil.getMap4Json(jsons);
				int kk = 0;// 有些列是隐藏的，所以ii为列的真正列号
				for (int i = 0; i < headermates.length; i++) {
					String[] headermate = headermates[i].split("@");
					String name = headermate[0];// 字段名
					String hidden = headermate[1];// 是否为隐藏字段
					if (hidden.equals("true")) {
						continue;
					}
					Object obj1 = map.get(name);
					HSSFCell cell = row.createCell(kk);
					// 设置单元格属性
					cell.setCellStyle(cellStyle);
					cell.setCellType(HSSFCell.CELL_TYPE_STRING);

					if (obj1 != null) {
						if (name.indexOf(".") > 0) {
							name = name.split("\\.")[1];
						}
						if (obj1.getClass().getName()
								.equals("java.lang.String")) {// 字段类型
							obj1 = OptionDictSupport.getCodeName(
									httpservletrequest, name, obj1
											+ "");
							cell.setCellValue((String) obj1);
						} else if (obj1.getClass().getName()
								.equals("java.sql.Date")) {// 字段类型
							cell.setCellValue(obj1.toString());
						}

					} else {
						cell.setCellValue("");
					}
					// System.out.println("输出第"+k+"行,"+"第"+i+"列");
					kk++;

				}
				k++;
			}

			// 把相应的Excel 工作簿存盘
			workbook.write(outputstream);
			// 操作结束，关闭文件
			outputstream.flush();
			outputstream.close();
			// 该处如果Excel过大会影响效率，谁有好的想法可以提出来参考（不过从页面下载完后就会清空）

		} catch (Exception ioexception) {

		}
	}
	
	public static void excelMapExport(HttpServletRequest httpservletrequest,
			List<?> listdata, OutputStream outputstream) throws Exception {

		// 创建新的Excel 工作簿
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 在Excel 工作簿中建一工作表
		HSSFSheet sheet = workbook.createSheet("Sheet1");
		// 设置单元格格式(文本)
		HSSFCellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("@"));
		String header_ = httpservletrequest.getParameter("header");
		 if(colsname!=null&&!colsname.equals("")){
		    	header_ =colsname;
		    }
		String[] headers = header_.split(",");
		String headermates_ = httpservletrequest.getParameter("headermate");
		if(cols!=null&&!cols.equals("")){
	    	headermates_ =cols;
	    }
		String[] headermates = headermates_.split(",");
		// 在索引0的位置创建行（第一行）
		HSSFRow row = sheet.createRow(0);

		int ii = 0;// 有些列是隐藏的，所以ii为列的真正列号
		for (int i = 0; i < headers.length; i++) {
			String[] headermate = headermates[i].split("@");// 截取列名，获取列的属性值，例如：列名，是否为隐藏字段
			if (headermate[1].equals("true")) {// true为隐藏字段
				continue;
			}

			HSSFCell cell = row.createCell(ii);// 第一列，标题
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(headers[i]);
			ii++;
		}

		try {
			int k = 1;// 行号
			// System.out.println("输出第"+k+"行");
			for (Iterator<?> iterator = listdata.iterator(); iterator.hasNext();) {

				Object obj2 = iterator.next();
				// 设置j行从第二行开始
				row = sheet.createRow(k);
				Map<String, String> map = (Map)obj2;
				int kk = 0;// 有些列是隐藏的，所以ii为列的真正列号
				for (int i = 0; i < headermates.length; i++) {
					String[] headermate = headermates[i].split("@");
					String name = headermate[0];// 字段名
					String hidden = headermate[1];// 是否为隐藏字段
					if (hidden.equals("true")) {
						continue;
					}
					Object obj1 = map.get(name);
					HSSFCell cell = row.createCell(kk);
					// 设置单元格属性
					cell.setCellStyle(cellStyle);
					cell.setCellType(HSSFCell.CELL_TYPE_STRING);

					if (obj1 != null) {
						
						
						if (name.indexOf(".") > 0) {
							name = name.split("\\.")[1];
						}if(name.indexOf("cityname")>=0&&obj1!=null&&!obj1.equals("")){
//							obj1=getCity(httpservletrequest,obj1.toString(),name);
							cell.setCellValue((String) obj1);
						}else if (obj1.getClass().getName()
								.equals("java.lang.String")) {// 字段类型
							obj1 = OptionDictSupport.getCodeName(
									httpservletrequest, name, obj1
											+ "");
							cell.setCellValue((String) obj1);
						} else if (obj1.getClass().getName()
								.equals("java.sql.Date")) {// 字段类型
							cell.setCellValue(obj1.toString());
						}else if (obj1.getClass().getName()
								.equals("java.lang.Integer")) {// 字段类型
							obj1 = OptionDictSupport.getCodeName(
									httpservletrequest, name, String.valueOf(obj1)
											+ "");
							cell.setCellValue((String) obj1);
						}

					} else {
						cell.setCellValue("");
					}
					// System.out.println("输出第"+k+"行,"+"第"+i+"列");
					kk++;

				}
				k++;
			}

			// 把相应的Excel 工作簿存盘
			workbook.write(outputstream);
			// 操作结束，关闭文件
			outputstream.flush();
			outputstream.close();
			// 该处如果Excel过大会影响效率，谁有好的想法可以提出来参考（不过从页面下载完后就会清空）

		} catch (Exception ioexception) {

		}
	}
	
//	public static  String getCity(HttpServletRequest request,String code,String name){
//		if(code==null||code.equals("")){
//			return "";
//		}
//		 Map<String,Object> citytree=(Map<String,Object>)request.getSession().getServletContext().getAttribute("city");
//		 String revalue="";
// 		City city=(City)citytree.get(code.toString().substring(0, 2)+"0000");
// 		revalue=city.getMc();
// 		if(code.toString().substring(0, 2).equals("99")){
// 			city=(City)citytree.get(code);
// 			revalue=revalue+"-"+city.getMc();
// 		}else{
// 			if(!code.toString().substring(2, 4).equals("00")){
// 	 			city=(City)citytree.get(code.toString().substring(0, 4)+"00");
// 	 			revalue=revalue+"-"+city.getMc();
// 	 		}
// 	 		if(!code.toString().substring(4, 6).equals("00")){
// 	 			city=(City)citytree.get(code);
// 	 			revalue=revalue+"-"+city.getMc();
// 	 		}
// 	 		if(name.split("_").length>1){
// 	 			if(name.split("_")[1].equals("s")){
// 	 				if(revalue.split("-").length==2){
// 	 	 				revalue=revalue.split("-")[0];
// 	 	 			}else{
// 	 	 				revalue="";
// 	 	 			}
// 	 			}
// 	 			else if(name.split("_")[1].equals("sx")){
// 	 				if(revalue.split("-").length==3){
// 	 	 				revalue=revalue.split("-")[1]+revalue.split("-")[2];
// 	 	 				
// 	 	 			}else if(revalue.split("-").length==2){
// 	 	 				revalue=revalue.split("-")[1];
// 	 	 			}else{
// 	 	 				revalue="";
// 	 	 			}	
// 	 			}else if(name.split("_")[1].equals("sf")){
// 	 				if(revalue.split("-").length>1){
// 	 					revalue=revalue.split("-")[0];
// 	 	 			}	
// 	 			}
// 	 				
// 	 		}
// 		}
// 		
// 		return revalue;
//	} 
	
	public  List<Object> excelInsert(File file,String pathconfig,HttpServletRequest request)
			throws Exception {
		Config config=new Config(pathconfig);
		HSSFWorkbook wb =readFile(file);
		System.out.println("Data dump:\n");
		for (int k = 0; k < wb.getNumberOfSheets(); k++) {
			HSSFSheet sheet = wb.getSheetAt(k);
			int rows = sheet.getPhysicalNumberOfRows();
			if(rows==0){
				continue;
			}
//			System.out.println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows
//					+ " row(s).");
		   final ArrayList<Object[]> arrayList=new ArrayList<Object[]>();
			for (int r = config.getDataIndex(); r < rows; r++) {
				HSSFRow row = sheet.getRow(r);
				if (row == null) {
					continue;
				}
				boolean flag=true;
				int cells = row.getPhysicalNumberOfCells();
//				System.out.println("\nROW " + row.getRowNum() + " has " + cells
//						+ " cell(s).");
				Object[] eColumns=new Object[cells]; 
				for (int c = 0; c < cells; c++) {
					HSSFCell cell = row.getCell(c);
					String value = null;
					Property property=config.getEntity().getPropertys().get(c);
					switch (cell.getCellType()) {

						case HSSFCell.CELL_TYPE_FORMULA:
							value = cell.getCellFormula();
							break;

						case HSSFCell.CELL_TYPE_NUMERIC:
							value =cell.getNumericCellValue()+"";
							break;

						case HSSFCell.CELL_TYPE_STRING:
							value =cell.getStringCellValue();
							break;

						default:
					}
					if(property.getParameter()!=null){//为参数字段
						property.setDataType("number");
						String codevalue=OptionDictSupport.getCodeValue(request,property.getParameter().getPtype(),value);
						value=codevalue;
					}else if(property.getCodeTalbe()!=null) {//为代码字段
						List list=getYzJdbcTemplate().queryForList("select "+property.getCodeTalbe().getPrimaryKey()+" from "+property.getCodeTalbe().getName()+" where "+property.getCodeTalbe().getReferenceColumn()+" = '"+value+"'");
						if(list!=null&&list.size()>0){
							value=((ListOrderedMap)(list.get(0))).get(property.getCodeTalbe().getPrimaryKey()).toString();
							
						}else{
							flag=false;
							break;
						}
							
						property.setDataType("number");
//						System.out.println("\n代码表字段  表名=" + property.getCodeTalbe().getName() + " 关联字段="
//								+ property.getCodeTalbe().getReferenceColumn());
					}
					
//					System.out.println("\n导入字段  col=" + property.getColumnName() + " 值="
//						+ value);
					EColumn eColumn=new EColumn();
			
					eColumn.setDataType(generateColumn(property.getDataType()));
					eColumn.setValue(value);
					eColumns[c]=eColumn;
								
				}
				if(flag){
					arrayList.add(eColumns);
					//System.out.println("\nsql=" + sqlrow);	
				}							
			}
			BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() {				
				public void setValues(PreparedStatement arg0, int arg1) throws SQLException {
					// TODO Auto-generated method stub
					Object[] objects=arrayList.get(arg1);
					for(int k=1;k<=objects.length;k++){
						EColumn ec=(EColumn)objects[k-1];
						arg0.setObject(k, ec.getValue(), ec.getDataType());
					}					
				}
				
				public int getBatchSize() {
					// TODO Auto-generated method stub
					return arrayList.size();
				}
			};
			
			getYzJdbcTemplate().batchUpdate(config.getSql(), setter);
		}
		return null;
		
	}
	private static int generateColumn(String dataType){
		int ret=12;
		if(dataType.equals("number")){
			ret=4;
		}else if(dataType.equals("sysdate")){
			ret=91;
		}
		else if(dataType.equals("date")){
			ret=91;
		}
		return ret;
		
	}
	public  List<ArrayList<Property>> excelToList(HttpServletRequest request,File file,String pathconfig)
			throws Exception {
		log.info("excel开始接受！");
		Config config=new Config(pathconfig);
		HSSFWorkbook wb =readFile(file);
		 ArrayList<ArrayList<Property>> arrayList=new ArrayList<ArrayList<Property>>();
		for (int k = 0; k < wb.getNumberOfSheets(); k++) {
			HSSFSheet sheet = wb.getSheetAt(k);
			int rows = sheet.getPhysicalNumberOfRows();
			if(rows==0){
				continue;
			}		  
			for (int r = config.getDataIndex(); r < rows; r++) {
				HSSFRow row = sheet.getRow(r);
				if (row == null) {
					continue;
				}
				//System.out.println(r);
				 ArrayList<Property> tempList=new ArrayList<Property>();
				int cells = config.getEntity().getPropertys().size();
				String emptycol=config.getEntity().getEmptycol()!=null?config.getEntity().getEmptycol()+",":null;
				boolean isemptycol=true;
 				for (int c = 0; c < cells; c++) {
					HSSFCell cell = row.getCell(c);
					if(cell==null){
						cell=row.createCell(c);
					}
					String value = null;
					Property property=new Property();
					BeanUtils.copyProperties(property, config.getEntity().getPropertys().get(c));
					
					
					
					switch (cell.getCellType()) {

						case HSSFCell.CELL_TYPE_FORMULA:
							value = cell.getCellFormula();
							property.setValue(getarameter(property,request,value));
							break;

						case HSSFCell.CELL_TYPE_NUMERIC:
							property.setDataType("number");
							DecimalFormat df = new DecimalFormat("0");   
							value = df.format(cell.getNumericCellValue()); 
							if(value.length()>=8){
								value = df.format(cell.getNumericCellValue()); 
							}else{
								value =cell.getNumericCellValue()+"";
							}
							property.setValue(getarameter(property,request,value));
							break;
							
						case HSSFCell.CELL_TYPE_STRING:
							value =cell.getStringCellValue();
							property.setValue(getarameter(property,request,value));
							break;
						case HSSFCell.CELL_TYPE_BLANK:
							property.setValue(property.getDefaultValue());
							break;
						default:
					}
					/**
					 * 判断是否空行
					 */
					if(emptycol.indexOf(property.getColumnName()+",")>=0){
						if(property.getValue()!=null&&!property.getValue().equals("")){
							isemptycol=isemptycol&&false;
						}else{
							isemptycol=isemptycol&&true;
						}
					}
					tempList.add(property);
								
				}
 				if(!isemptycol){
 					arrayList.add(tempList);	
 				}
			}
			
		}
		log.info("excel接受完毕！");
		return arrayList;		
	}
	private String getarameter(Property property,HttpServletRequest request,String value){
		if(value.length()>2&&value.substring(value.length()-2).equals(".0")){
			value=String.valueOf(value.replace(".0", ""));
		}
		if(property.getParameter()!=null){//为参数字段
			property.setDataType("number");
			String codevalue=OptionDictSupport.getCodeValue(request,property.getParameter().getPtype(),value.toString());
			value=codevalue;
		}else if(property.getCodeTalbe()!=null) {//为代码字段
			List list=getYzJdbcTemplate().queryForList("select "+property.getCodeTalbe().getPrimaryKey()+" from "+property.getCodeTalbe().getName()+" where "+property.getCodeTalbe().getReferenceColumn()+" = '"+value+"'");
			if(list!=null&&list.size()>0){
				value=((ListOrderedMap)(list.get(0))).get(property.getCodeTalbe().getPrimaryKey()).toString();
				
			}
			property.setDataType("number");
//			System.out.println("\n代码表字段  表名=" + property.getCodeTalbe().getName() + " 关联字段="
//					+ property.getCodeTalbe().getReferenceColumn());
		}
		return value;
		
	}
	
	
	
	public static void excelExp(HttpServletRequest httpservletrequest,
			 List<?> listdata,HttpServletResponse response,Map fieldmap,String filename) throws Exception{
	 javax.servlet.ServletOutputStream servletoutputstream = response.getOutputStream();
		      response.reset();//必须加，不然保存不了临时文件     
		      response.setContentType("application/vnd.ms-excel;charset=GBK");
		      if(filename!=null&&!filename.equals("")){
		    	  filename = new String((filename+".xls").getBytes("gb2312"),"iso8859-1");//取消乱码   
		    	  response.setHeader("Content-Disposition","attachment; filename=" +filename);     
		      }		     
		      excelExp(httpservletrequest, listdata, servletoutputstream, fieldmap);
}
	
	public static void excelExp(HttpServletRequest httpservletrequest,
			 List<?> listdata, OutputStream outputstream,Map fieldmap)
			throws Exception { 

			    // 创建新的Excel 工作簿
			    HSSFWorkbook workbook = new HSSFWorkbook();
			    // 在Excel 工作簿中建一工作表
			    HSSFSheet sheet = workbook.createSheet("Sheet1");
			    // 设置单元格格式(文本)
			    HSSFCellStyle cellStyle = workbook.createCellStyle();
			    cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("@"));
			    // 在索引0的位置创建行（第一行）
			    HSSFRow row = sheet.createRow(0);
			    
			    int ii=0;//有些列是隐藏的，所以ii为列的真正列号
			    
			    Iterator iter = fieldmap.entrySet().iterator(); 
			    while (iter.hasNext()) { 
		   	    	Map.Entry entry = (Map.Entry) iter.next(); 
		   	    	 Object field= entry.getKey();
		   	    	 Object fieldname=entry.getValue();
		   	  	HSSFCell cell=row.createCell(ii);// 第一列，标题
		    	cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		    	cell.setCellValue(fieldname.toString());
		    	ii++;
		   	    	
		    		}
			     try {
			   int k=1;//行号
			   //System.out.println("输出第"+k+"行");
			   for (Iterator<?> iterator = listdata.iterator(); iterator
								.hasNext();) {
				   
				   Object obj2 = iterator.next();
				   
				   //设置j行从第二行开始
				      row = sheet.createRow(k);
				  Map map=(Map)obj2;
				  int kk=0;//有些列是隐藏的，所以ii为列的真正列号
				  iter = fieldmap.entrySet().iterator();
				  while (iter.hasNext()) { 
					  Map.Entry entry = (Map.Entry) iter.next(); 
			   	    	 Object field= entry.getKey();
			   	    	 Object fieldname=entry.getValue();
					   Object obj1 = map.get(field.toString().toUpperCase());
					       HSSFCell cell = row.createCell(kk);
					       // 设置单元格属性
					       cell.setCellStyle(cellStyle);
					       cell.setCellType(HSSFCell.CELL_TYPE_STRING);
					      
					       if (obj1 != null) {
					    	   cell.setCellValue(obj1.toString());					    	   
						   }else {
							   cell.setCellValue("");
						   }
					       //System.out.println("输出第"+k+"行,"+"第"+i+"列");
					       kk++;
			       
			      }
				   	k++;
			     }
			    
			    // 把相应的Excel 工作簿存盘
			    workbook.write(outputstream);
			    // 操作结束，关闭文件
			    outputstream.flush();
			    outputstream.close();
			     //该处如果Excel过大会影响效率，谁有好的想法可以提出来参考（不过从页面下载完后就会清空）
			   
			    
			    } catch (Exception ioexception) {
			    	
			   }
}
	
	
	/**
	 * creates an {@link HSSFWorkbook} the specified OS filename.
	 */
	private static HSSFWorkbook readFile(File file) throws IOException {
		return new HSSFWorkbook(new FileInputStream(file));
	}
	public JdbcTemplate getYzJdbcTemplate() {
		return yzJdbcTemplate;
	}
	public void setYzJdbcTemplate(JdbcTemplate yzJdbcTemplate) {
		this.yzJdbcTemplate = yzJdbcTemplate;
	}

	public static String getCols() {
		return cols;
	}

	public static void setCols(String cols) {
		DoExcel.cols = cols;
	}

	public static String getColsname() {
		return colsname;
	}

	public static void setColsname(String colsname) {
		DoExcel.colsname = colsname;
	}
	
	
	
}
